﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

using iTool.Cloud.Database.SqlStructureProvider.Options;

using Microsoft.Data.Sqlite;

namespace iTool.Cloud.Database.SqlStructureProvider
{
    public class SqliteStructureProvider : ISqlStructureProvider
    {
        const string DirectoryPath = "./Storage";
        const string DataSourceVersion = DirectoryPath + "/Versions/{0}.version";

        // AUTOINCREMENT 自增
        const string PRIMARY_KEY_KEYWORD = "_IDX";
        const string VALUE_NULL = "NULL";
        const string VALUE_NOT_NULL = "NOT NULL";
        const string PRIMARY_KEY_KEYWORD_TYPE = "INTEGER PRIMARY KEY";
        const string ADD_FIELD_SQL = "ALTER TABLE {0} ADD COLUMN {1} {2}";
        const string CREATE_FIELD_SQL = "{0} {1} {2}";

        readonly SqliteConnectionStringBuilder connectionStringBuilder;
        readonly string table;

        private IEnumerable<FieldOptions> FieldOptions;
        private IEnumerable<string> Fields;

        public SqliteStructureProvider(string table, SqliteConnectionStringBuilder connectionStringBuilder)
        {
            this.table = table;
            this.connectionStringBuilder = connectionStringBuilder;

            // version
            using (var connection = new SqliteConnection(new SqliteConnectionStringBuilder
            {
                DataSource = String.Format(DataSourceVersion, table),
                Mode = SqliteOpenMode.ReadWriteCreate,
                Cache = SqliteCacheMode.Private,
                Pooling = true
            }.ToString()))
            {
                connection.Open();
                using (SqliteCommand command = connection.CreateCommand())
                {
                    // isCreateIndex != 1 未创建 
                    command.CommandText = @$"CREATE TABLE IF NOT EXISTS {table}(action varchar, keys varchar, sql varchar,extend varchar,tranId long,isCreateIndex int)";
                    command.ExecuteNonQuery();

                    command.CommandText = $"SELECT 1 FROM sqlite_master WHERE type = 'index' and name = 'index_reader'";
                    var reader = command.ExecuteReader();
                    if (!reader.HasRows)
                    {
                        reader.Close();
                        reader.DisposeAsync();
                        // 创建索引
                        command.CommandText = $"CREATE INDEX index_reader on {table}(isCreateIndex)";
                        command.ExecuteNonQuery();
                    }
                }
            }

            // base db
            using (var connection = new SqliteConnection(this.connectionStringBuilder.ToString()))
            {
                connection.Open();
                using (SqliteCommand command = new SqliteCommand(@$"CREATE TABLE IF NOT EXISTS {this.table}({string.Format(CREATE_FIELD_SQL, PRIMARY_KEY_KEYWORD, PRIMARY_KEY_KEYWORD_TYPE, VALUE_NOT_NULL)})", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            this.FieldOptions = this.GetAllFieldsAsync().Result;
            this.Fields = this.FieldOptions.Select(item => item.Name);
        }

        public async Task AddFieldAsync(FieldOptions field)
        {
            await using (var connection = new SqliteConnection(this.connectionStringBuilder.ToString()))
            {
                await connection.OpenAsync();
                await using(SqliteCommand command = new SqliteCommand(string.Format(ADD_FIELD_SQL, this.table, field.Name, field.GetValueType()), connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }
        
        public async Task AddFieldsAsync(IEnumerable<string> fields)
        {
            var sqls = fields.Select(field => string.Format(ADD_FIELD_SQL, this.table, field, "Any"));
            await using (var connection = new SqliteConnection(this.connectionStringBuilder.ToString()))
            {
                await connection.OpenAsync();
                await using (SqliteCommand command = new SqliteCommand(String.Join(';', sqls), connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }
        
        public async Task AddFieldsAsync(IEnumerable<FieldOptions> fields)
        {
            var sqls = fields.Select(field => string.Format(ADD_FIELD_SQL, this.table, field.Name, field.GetValueType()));
            await using (var connection = new SqliteConnection(this.connectionStringBuilder.ToString()))
            {
                await connection.OpenAsync();
                await using (SqliteCommand command = new SqliteCommand(String.Join(';', sqls), connection))
                {
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        public async Task CheckStructureAsync(IEnumerable<string> fields)
        {
            var missaFields = fields.Except(this.Fields);
            if (missaFields.Any())
            {
                await this.AddFieldsAsync(missaFields);
                this.Fields = this.Fields.Concat(missaFields);
            }
        }

        public async Task<IEnumerable<FieldOptions>> GetAllFieldsAsync()
        {
            // 查询所有Table
            // [{"name":"Users"},{"name":"sqlite_sequence"}]
            //var result = test.ExecuteQuery("select name from sqlite_master where type='table' order by name;");

            // 查询所有列
            //{
            //    "cid":1,
            //    "name":"Username",
            //    "type":"NVARCHAR(64)",
            //    "notnull":1,
            //    "dflt_value":null,
            //    "pk":0
            //}
            //result = test.ExecuteQuery("PRAGMA table_info(users)");

            List<FieldOptions> fieldOptions = new List<FieldOptions>();

            await using (var connection = new SqliteConnection(this.connectionStringBuilder.ToString()))
            {
                await connection.OpenAsync();
                await using (SqliteCommand command = new SqliteCommand($"PRAGMA table_info({this.table})", connection))
                {
                    var reader = await command.ExecuteReaderAsync();

                    while (reader.Read())
                    {
                        Type typeValue = typeof(string);
                        switch (reader["type"].ToString())
                        {
                            case "INTEGER":
                                typeValue = typeof(long);
                                break;
                            case "REAL":
                                typeValue = typeof(decimal);
                                break;
                            case "BLOB":
                                typeValue = typeof(byte);
                                break;
                        }

                        fieldOptions.Add(new FieldOptions
                        {
                            Name = reader["name"].ToString(),
                            ValueType = typeValue
                        });
                    }

                    return fieldOptions;
                }
            }

        }

    }
}
